Imports Microsoft.VisualBasic
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports EntityLayer

Public Class ProveedorDA

    Public Shared Function BuscarProveedorxProducto(ByVal intCodProducto As Integer) As Proveedor

        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myCommand As New SqlCommand("usp_ListProveedorByProducto", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim para As SqlParameter

        para = New SqlParameter("@intIdProducto", SqlDbType.Int)
        para.Direction = ParameterDirection.Input
        para.Value = intCodProducto
        myCommand.Parameters.Add(para)

        myConnection.Open()

        Dim reader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Dim rsProveedor As New Proveedor()

        While reader.Read()
            rsProveedor.Codigo = reader("Codigo")
            rsProveedor.RazonSocial = reader("RazonSocial").ToString()
        End While

        reader.Close()
        myConnection.Close()

        Return rsProveedor

    End Function

    Public Shared Function ObtenerNuevoCodigo() As String

        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myCommand As New SqlCommand("usp_ListNuevoIdProveedor", myConnection)
        myCommand.CommandType = CommandType.StoredProcedure

        Dim para As SqlParameter

        para = New SqlParameter("@intCodProveedor", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Output
        myCommand.Parameters.Add(para)

        myConnection.Open()

        myCommand.ExecuteNonQuery()

        myConnection.Close()

        Return myCommand.Parameters("@intCodProveedor").Value

    End Function

    Public Shared Function Agregar(ByVal ProveedorEN As Proveedor) As Boolean
        Try
            Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
            Dim myCommand As New SqlCommand("usp_AddPostulanteProveedor", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter

            para = New SqlParameter("@strCodigo", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Codigo
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strRUC", SqlDbType.VarChar, 11)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.RUC
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strRazonSocial", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.RazonSocial
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strDireccion", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Direccion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strRubro", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Rubro
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strContacto", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Contacto
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strTelefono", SqlDbType.VarChar, 20)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Telefono
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strEmail", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Email
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strUrl", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Url
            myCommand.Parameters.Add(para)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return True
        Catch ex As Exception
            Dim E As String = ex.Message
            Return False
        End Try

    End Function

    Public Shared Function Actualizar(ByVal ProveedorEN As Proveedor) As Boolean
        Try
            Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
            Dim myCommand As New SqlCommand("usp_UdpPostulanteProveedor", myConnection)
            myCommand.CommandType = CommandType.StoredProcedure

            Dim para As SqlParameter

            para = New SqlParameter("@strCodigo", SqlDbType.VarChar, 5)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Codigo
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strRUC", SqlDbType.VarChar, 11)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.RUC
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strRazonSocial", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.RazonSocial
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strDireccion", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Direccion
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strRubro", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Rubro
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strContacto", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Contacto
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strTelefono", SqlDbType.VarChar, 20)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Telefono
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strEmail", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Email
            myCommand.Parameters.Add(para)

            para = New SqlParameter("@strUrl", SqlDbType.VarChar, 100)
            para.Direction = ParameterDirection.Input
            para.Value = ProveedorEN.Url
            myCommand.Parameters.Add(para)

            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()

            Return True
        Catch ex As Exception
            Dim E As String = ex.Message
            Return False
        End Try

    End Function

    Public Shared Function ListarTodo() As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_Proveedor_ListAllCatalogo", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        myDataadapter.Fill(ds, "Proveedor")

        Return ds

    End Function

    Public Shared Function ListarxCodigo(ByVal strCodigo As String) As DataSet
        Dim myConnection As New SqlConnection(ConexionDA.ObtenerCnx)
        Dim myDataadapter As New SqlDataAdapter("COM_Proveedor_ListByCodigo", myConnection)
        myDataadapter.SelectCommand.CommandType = CommandType.StoredProcedure

        Dim ds As New DataSet

        Dim para As SqlParameter

        para = New SqlParameter("@strCodigo", SqlDbType.VarChar, 5)
        para.Direction = ParameterDirection.Input
        para.Value = strCodigo
        myDataadapter.SelectCommand.Parameters.Add(para)

        myDataadapter.Fill(ds, "Proveedor")

        Return ds

    End Function
End Class
